#pragma once

#include <mysql/mysql.h>
#include <iostream>
#include <string>
#include <vector>
#include <cstring>
#include <cctype>
#include <sstream>
#include <ctime>
#include <iomanip>
#include "../comm/log.hpp"
#include "../comm/util.hpp"
#include "../DB_table/Meta_data.h"

using namespace ns_log;
using namespace ns_util;

// 定义时间格式枚举类型，方便选择不同的常用时间格式
enum class TimeFormat
{
    DEFAULT,    // 例如：2024-01-01 12:00:00
    SHORT_DATE, // 例如：2024-01-01
    SHORT_TIME, // 例如：12:00:00
    CUSTOM      // 自定义格式，通过传入的格式字符串指定
};

// 获取当前时间并格式化的函数
std::string GetFormattedCurrentTime(TimeFormat format = TimeFormat::DEFAULT, const std::string &customFormat = "")
{
    std::time_t currentTime = std::time(nullptr);
    std::tm *timeInfo = std::localtime(&currentTime);

    std::stringstream ss;
    switch (format)
    {
    case TimeFormat::DEFAULT:
        ss << std::put_time(timeInfo, "%Y-%m-%d %H:%M:%S");
        break;
    case TimeFormat::SHORT_DATE:
        ss << std::put_time(timeInfo, "%Y-%m-%d");
        break;
    case TimeFormat::SHORT_TIME:
        ss << std::put_time(timeInfo, "%H:%M:%S");
        break;
    case TimeFormat::CUSTOM:
        ss << std::put_time(timeInfo, customFormat.c_str());
        break;
    }

    return ss.str();
}

template <typename T>
std::vector<TableColumnInfo> buildTableColumns(const std::vector<std::string> &selectedColumns = {})
{
    if constexpr (std::is_same_v<T, Users>)
    {
        std::vector<TableColumnInfo> columns;
        if (selectedColumns.empty())
        {
            // 如果没有指定列，返回全部列信息
            columns = {
                {"UserID", ColumnDataType::INT_TYPE},
                {"UserName", ColumnDataType::STRING_TYPE},
                {"Password", ColumnDataType::STRING_TYPE},
                {"Create_time", ColumnDataType::STRING_TYPE},
                {"Update_time", ColumnDataType::STRING_TYPE}};
        }
        else
        {
            // 根据指定的列名构建列信息向量
            for (const auto &colName : selectedColumns)
            {
                if (colName == "UserID")
                    columns.push_back({"UserID", ColumnDataType::INT_TYPE});
                else if (colName == "UserName")
                    columns.push_back({"UserName", ColumnDataType::STRING_TYPE});
                else if (colName == "Password")
                    columns.push_back({"Password", ColumnDataType::STRING_TYPE});
                else if (colName == "Create_time")
                    columns.push_back({"Create_time", ColumnDataType::STRING_TYPE});
                else if (colName == "Update_time")
                    columns.push_back({"Update_time", ColumnDataType::STRING_TYPE});
                else
                {
                    std::cerr << "Invalid column name for Users table: " << colName << std::endl;
                }
            }
        }
        return columns;
    }
    else if constexpr (std::is_same_v<T, Emails>)
    {
        std::vector<TableColumnInfo> columns;
        if (selectedColumns.empty())
        {
            columns = {
                {"EmailID", ColumnDataType::INT_TYPE},
                {"UserID", ColumnDataType::INT_TYPE},
                {"Email", ColumnDataType::STRING_TYPE},
                {"IsVerified", ColumnDataType::BOOL_TYPE},
                {"Create_time", ColumnDataType::STRING_TYPE}};
        }
        else
        {
            for (const auto &colName : selectedColumns)
            {
                if (colName == "EmailID")
                    columns.push_back({"EmailID", ColumnDataType::INT_TYPE});
                else if (colName == "UserID")
                    columns.push_back({"UserID", ColumnDataType::INT_TYPE});
                else if (colName == "Email")
                    columns.push_back({"Email", ColumnDataType::STRING_TYPE});
                else if (colName == "IsVerified")
                    columns.push_back({"IsVerified", ColumnDataType::BOOL_TYPE});
                else if (colName == "Create_time")
                    columns.push_back({"Create_time", ColumnDataType::STRING_TYPE});
                else
                {
                    std::cerr << "Invalid column name for Emails table: " << colName << std::endl;
                }
            }
        }
        return columns;
    }
    else if constexpr (std::is_same_v<T, MachineCodes>)
    {
        std::vector<TableColumnInfo> columns;
        if (selectedColumns.empty())
        {
            columns = {
                {"MachineID", ColumnDataType::INT_TYPE},
                {"UserID", ColumnDataType::INT_TYPE},
                {"MachineCode", ColumnDataType::STRING_TYPE},
                {"Create_time", ColumnDataType::STRING_TYPE}};
        }
        else
        {
            for (const auto &colName : selectedColumns)
            {
                if (colName == "MachineID")
                    columns.push_back({"MachineID", ColumnDataType::INT_TYPE});
                else if (colName == "UserID")
                    columns.push_back({"UserID", ColumnDataType::INT_TYPE});
                else if (colName == "MachineCode")
                    columns.push_back({"MachineCode", ColumnDataType::STRING_TYPE});
                else if (colName == "Create_time")
                    columns.push_back({"Create_time", ColumnDataType::STRING_TYPE});
                else
                {
                    std::cerr << "Invalid column name for MachineCodes table: " << colName << std::endl;
                }
            }
        }
        return columns;
    }
    else if constexpr (std::is_same_v<T, EmailMachineAuths>)
    {
        std::vector<TableColumnInfo> columns;
        if (selectedColumns.empty())
        {
            columns = {
                {"AuthID", ColumnDataType::INT_TYPE},
                {"EmailID", ColumnDataType::INT_TYPE},
                {"MachineID", ColumnDataType::INT_TYPE},
                {"AuthCode", ColumnDataType::STRING_TYPE},
                {"IsVerified", ColumnDataType::BOOL_TYPE},
                {"Create_time", ColumnDataType::STRING_TYPE}};
        }
        else
        {
            for (const auto &colName : selectedColumns)
            {
                if (colName == "AuthID")
                    columns.push_back({"AuthID", ColumnDataType::INT_TYPE});
                else if (colName == "EmailID")
                    columns.push_back({"EmailID", ColumnDataType::INT_TYPE});
                else if (colName == "MachineID")
                    columns.push_back({"MachineID", ColumnDataType::INT_TYPE});
                else if (colName == "AuthCode")
                    columns.push_back({"AuthCode", ColumnDataType::STRING_TYPE});
                else if (colName == "IsVerified")
                    columns.push_back({"IsVerified", ColumnDataType::BOOL_TYPE});
                else if (colName == "Create_time")
                    columns.push_back({"Create_time", ColumnDataType::STRING_TYPE});
                else
                {
                    std::cerr << "Invalid column name for EmailMachineAuths table: " << colName << std::endl;
                }
            }
        }
        return columns;
    }
    else
    {
        std::cerr << "Unsupported table type for building columns." << std::endl;
        return {};
    }
}

// 通用的赋值函数，根据数据类型进行不同的赋值操作，减少重复代码
template <typename T, typename MemberType>
void assignValue(T &data, const char *field_value, MemberType T::*memberPtr)
{
    if constexpr (std::is_same_v<MemberType, int>)
    {
        if (field_value != nullptr)
        {
            std::stringstream ss(field_value);
            ss >> data.*memberPtr;
        }
    }
    else if constexpr (std::is_same_v<MemberType, std::string>)
    {
        if (field_value != nullptr)
        {
            //std::cout << "field_value:" << field_value << std::endl;
            data.*memberPtr = field_value;
        }
    }
     else if constexpr (std::is_same_v<MemberType, bool>) {
        static const char* trueValues[] = {"1", "true", "TRUE", "True", "yes", "YES", "Yes", "on", "ON", "On"};
        for (const char* trueVal : trueValues) {
            if (strcasecmp(field_value, trueVal) == 0) {
                data.*memberPtr = true;
                return;
            }
        }
        data.*memberPtr = false;
    }
}

template <typename T>
bool DatabaseOperation(MYSQL *my, const std::string &sql, OperationType opType, const std::vector<TableColumnInfo> &tableColumns, std::string *out, std::vector<T> *OutTab = nullptr)
{
    if (opType == OperationType::INSERT)
    {
        // 执行插入操作
        if (mysql_query(my, sql.c_str()) != 0)
        {
            std::cout << "Insert error: " << mysql_error(my) << std::endl;
            char buffer[256]; // 假设足够大的缓冲区，实际要根据可能的最长拼接结果合理设置大小
            strcpy(buffer, "Insert error: ");
            strcat(buffer, mysql_error(my));
            *out = buffer;
            return false;
        }
        return true;
    }
    else if (opType == OperationType::SELECT)
    {
        // 执行查询操作
        if (mysql_query(my, sql.c_str()) != 0)
        {
            std::cerr << "Query error: " << mysql_error(my) << std::endl;
            char buffer[256]; // 假设足够大的缓冲区，实际要根据可能的最长拼接结果合理设置大小
            strcpy(buffer, "Query error: ");
            strcat(buffer, mysql_error(my));
            *out = buffer;
            return false;
        }
        MYSQL_RES *res = mysql_store_result(my);
        if (res == nullptr)
        {
            std::cerr << "Error storing result: " << mysql_error(my) << std::endl;
            char buffer[256]; // 假设足够大的缓冲区，实际要根据可能的最长拼接结果合理设置大小
            strcpy(buffer, "Error storing error: ");
            strcat(buffer, mysql_error(my));
            *out = buffer;
            return false;
        }
        int rows = mysql_num_rows(res);
        int cols = mysql_num_fields(res);
        std::cout << rows << "   " << cols << "   " << sql << std::endl;
        for (int i = 0; i < rows; i++)
        {
            MYSQL_ROW row = mysql_fetch_row(res);
            //std::cout << "fetchdown" << std::endl;
            if (std::string(typeid(T).name()) == typeid(Users).name())
            {
                Users data;
                size_t colIndex = 0;
                for (const auto &colInfo : tableColumns)
                {
                    const char *field_value = row[colIndex];

                    if (colInfo.columnName == "UserID")
                    {
                        assignValue(data, field_value, &Users::UserID);
                    }
                    else if (colInfo.columnName == "UserName")
                    {
                        assignValue(data, field_value, &Users::UserName);
                    }
                    else if (colInfo.columnName == "Password")
                    {
                        assignValue(data, field_value, &Users::Password);
                    }
                    else if (colInfo.columnName == "Create_time")
                    {
                        assignValue(data, field_value, &Users::Create_time);
                    }
                    else if (colInfo.columnName == "Update_time")
                    {
                        assignValue(data, field_value, &Users::Update_time);
                    }

                    colIndex++;
                }
                if (OutTab != nullptr)
                {
                    if constexpr (std::is_same_v<T, Users>)
                    { // 添加类型检查
                        OutTab->push_back(data);
                    }
                }
            }
            else if (std::string(typeid(T).name()) == typeid(Emails).name())
            {
                Emails data;
                size_t colIndex = 0;
                for (const auto &colInfo : tableColumns)
                {
                    const char *field_value = row[colIndex];
                    std::cout << colInfo.columnName << std::endl;
                    if (colInfo.columnName == "EmailID")
                    {
                        assignValue(data, field_value, &Emails::EmailID);
                    }
                    else if (colInfo.columnName == "UserID")
                    {
                        assignValue(data, field_value, &Emails::UserID);
                    }
                    else if (colInfo.columnName == "Email")
                    {
                        
                        assignValue(data, field_value, &Emails::Email);
                    }
                    else if (colInfo.columnName == "IsVerified")
                    {
                        
                        if (field_value == nullptr)
                        {
                            std::cerr << "field_value is null for IsVerified" << std::endl;
                            throw std::invalid_argument("field_value is null for IsVerified");
                        }
                        assignValue(data, field_value, &Emails::IsVerified);
                        
                    }
                    else if (colInfo.columnName == "Create_time")
                    {
                        assignValue(data, field_value, &Emails::Create_time);
                    }

                    colIndex++;
                }
                if (OutTab != nullptr)
                {
                    if constexpr (std::is_same_v<T, Emails>)
                    { // 添加类型检查
                        OutTab->push_back(data);
                    }
                }
            }
            else if (std::string(typeid(T).name()) == typeid(MachineCodes).name())
            {
                MachineCodes data;
                size_t colIndex = 0;
                for (const auto &colInfo : tableColumns)
                {
                    const char *field_value = row[colIndex];

                    if (colInfo.columnName == "MachineID")
                    {
                        assignValue(data, field_value, &MachineCodes::MachineID);
                    }
                    else if (colInfo.columnName == "UserID")
                    {
                        assignValue(data, field_value, &MachineCodes::UserID);
                    }
                    else if (colInfo.columnName == "MachineCode")
                    {
                        assignValue(data, field_value, &MachineCodes::MachineCode);
                    }
                    else if (colInfo.columnName == "Create_time")
                    {
                        assignValue(data, field_value, &MachineCodes::Create_time);
                    }

                    colIndex++;
                }
                if (OutTab != nullptr)
                {
                    if constexpr (std::is_same_v<T, MachineCodes>)
                    { // 添加类型检查
                        OutTab->push_back(data);
                    }
                }
            }
            else if (std::string(typeid(T).name()) == typeid(EmailMachineAuths).name())
            {
                EmailMachineAuths data;
                size_t colIndex = 0;
                for (const auto &colInfo : tableColumns)
                {
                    const char *field_value = row[colIndex];

                    if (colInfo.columnName == "AuthID")
                    {
                        assignValue(data, field_value, &EmailMachineAuths::AuthID);
                    }
                    else if (colInfo.columnName == "EmailID")
                    {
                        assignValue(data, field_value, &EmailMachineAuths::EmailID);
                    }
                    else if (colInfo.columnName == "MachineID")
                    {
                        assignValue(data, field_value, &EmailMachineAuths::MachineID);
                    }
                    else if (colInfo.columnName == "AuthCode")
                    {
                        assignValue(data, field_value, &EmailMachineAuths::AuthCode);
                    }
                    else if (colInfo.columnName == "IsVerified")
                    {
                        assignValue(data, field_value, &EmailMachineAuths::IsVerified);
                    }
                    else if (colInfo.columnName == "Create_time")
                    {
                        assignValue(data, field_value, &EmailMachineAuths::Create_time);
                    }

                    colIndex++;
                }
                if (OutTab != nullptr)
                {
                    if constexpr (std::is_same_v<T, EmailMachineAuths>)
                    { // 添加类型检查
                        OutTab->push_back(data);
                    }
                }
            }
            else
            {
                std::cerr << "Unrecognized table type for assignment." << std::endl;
            }
        }

        mysql_free_result(res);
        return true;
    }
    return false;
}


MYSQL *init_MYSQL()
{
    MYSQL *my = mysql_init(nullptr);

    // 连接数据库
    if (nullptr == mysql_real_connect(my, host.c_str(), DB_user.c_str(), DB_passwd.c_str(), DB.c_str(), port, nullptr, 0))
    {
        LOG(FATAL) << "连接数据库失败!" << "\n";
        return nullptr;
    }
    mysql_set_character_set(my, "utf8");
    LOG(INFO) << "连接数据库成功!" << "\n";
    return my;
}

bool CheckEmailExists(MYSQL *my, const std::string &Email, std::string *out, std::vector<Emails>* emails) {
    std::string SelectSql = "SELECT * FROM " + Emails_Tab + " WHERE Email = '" + Email + "';";
    if (!DatabaseOperation<Emails>(my, SelectSql, OperationType::SELECT, buildTableColumns<Emails>(), out, emails)) {
        std::cout << "select email!" << std::endl;
        return false;
    }
    std::cout << "emails size: " << emails->size() << std::endl;
    if (emails->size() == 0) {
        *out = "验证码错误";
        return false;
    }
    std::cout << "check" << std::endl;
    return true;
}
bool GetEmailIdByEmail(MYSQL *my, const std::string &Email, std::string *out, int *EmailId) {
    std::string sql = "SELECT EmailID FROM " + Emails_Tab + " WHERE Email = '" + Email + "';";
    std::vector<Emails> Res;
    if (!DatabaseOperation<Emails>(my, sql, OperationType::SELECT, buildTableColumns<Emails>({"EmailID"}), out, &Res)) {
        std::cout << "select Last_insert_error!" << std::endl;
        return false;
    }

    if (Res.size() != 0) {
        *EmailId = Res[0].EmailID;
        return true;
    } else {
        *out = "Email not found";
        return false;
    }
}

bool GetMachineIdByMachineCode(MYSQL *my, const std::string &Machinecode, std::string *out, int *Machine_Id) {
    std::string sql = "SELECT MachineID FROM " + MachineCodes_Tab + " WHERE MachineCode = '" + Machinecode + "';";
    std::vector<MachineCodes> Res1;
    if (!DatabaseOperation<MachineCodes>(my, sql, OperationType::SELECT, buildTableColumns<MachineCodes>({"MachineID"}), out, &Res1)) {
        std::cout << "select Last_insert_error!" << std::endl;
        return false;
    }

    if (Res1.size() != 0) {
        *Machine_Id = Res1[0].MachineID;
        return true;
    } else {
        *out = "MachineCode not found";
        return false;
    }
}
bool GetEmailMachineAuths(MYSQL *my, int EmailId, int Machine_Id, std::string *out, std::vector<EmailMachineAuths> *emailMachineAuths) {
    std::string SelectSql = "SELECT AuthCode, IsVerified FROM EmailMachineAuths WHERE EmailID = " + std::to_string(EmailId) + " AND MachineID = " + std::to_string(Machine_Id) + ";";
    if (!DatabaseOperation<EmailMachineAuths>(my, SelectSql, OperationType::SELECT, buildTableColumns<EmailMachineAuths>({"AuthCode", "IsVerified"}), out, emailMachineAuths)) {
        std::cout << "select Last_insert_error!" << std::endl;
        return false;
    }
    return true;
}
bool CheckUserNameExists(MYSQL *my, const std::string &User, std::string *out) {
    std::string SelectSql = "SELECT * FROM " + Users_Tab + " WHERE UserName = '" + User + "';";
    std::vector<Users> users;
    if (!DatabaseOperation<Users>(my, SelectSql, OperationType::SELECT, buildTableColumns<Users>(), out, &users)) {
        std::cout << "查询用户数据失败" << std::endl;
        return false;
    }

    if (users.size() != 0) {
        *out = "用户名已存在";
        return true;
    }

    return false;
}